package uk.ac.tess.timemanagement.todolist;

import java.util.ArrayList;
import java.util.List;

import uk.ac.tess.timemanagement.MainActivity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ToDoDatabaseHandler extends SQLiteOpenHelper{
	
	private static final String sToDoTableName = "todolist";
	
	/**
	 * The name of the property (id) used as primary key in the table.
	 */
	private static final String sColumnID = "_id";

	/**
	 * The name of the property (is active).
	 */
	private static final String sColumnIsActive= "isactive";

	/**
	 * The name of the property (description).
	 */
	private static final String sColumnDescription = "description";

	/**
	 * The name of the property (date).
	 */
	private static final String sColumnDate = "date";
	
	
	/**
	 * The name of the property (time).
	 */
	private static final String sColumnTime = "time";
	
	/**
	 * Create a helper object to create, open, and/or manage a database.
	 * 
	 * @param context
	 *            The context which the database needs to be created.
	 */
	public ToDoDatabaseHandler (Context context) {
		super(context, MainActivity.sDatabaseName, null, 1);
	}
	
	/**
	 * Called when the database is created for the first time.
	 * 
	 * @param db
	 *            The database.
	 */
	public void onCreate(SQLiteDatabase db) {
		// The create command that defines the database model.
		String sCommand = "CREATE TABLE " + sToDoTableName + " (" +
				sColumnID	+ " INTEGER PRIMARY KEY, \n" +
				sColumnIsActive + " INTEGER,\n" + // 0 - represents not active, 1 - represents active
				sColumnDescription + " TEXT,\n" + 
				sColumnDate + " TEXT,\n " +
				sColumnTime + " TEXT" + " )";
		db.execSQL(sCommand);
	}
	
	/**
	 * Called when the database needs to be upgraded. Only relevant when you
	 * have multiple versions of the database scheme in play.
	 * 
	 * @param db
	 *            The database.
	 * @param oldVersion
	 *            The old database version.
	 * @param newVersion
	 *            The new database version.
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + sToDoTableName);
		onCreate(db);
	}
	
	/**
	 * Removes all the data from the database.
	 */
	public void removeAll() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + sToDoTableName);
		onCreate(db);
		db.close();
	}
	
	/**
	 * Removes a {@link ToDoItem} from the database.
	 * 
	 * @param id
	 *            The {@link ToDoItem} id to be removed.
	 *            
	 * @return Returns a {@link Boolean} true if the {@link ToDoItem} has been removed.
	 */
	public boolean removeToDoItem(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		String removeQuery = "DELETE FROM " + sToDoTableName + " WHERE "
				+ sColumnID + " IN (SELECT " + sColumnID + " FROM "
				+ sToDoTableName + " WHERE " + sColumnID + " = '" + id
				+ "' LIMIT 1);";
		db.execSQL(removeQuery);
		db.close();
		return true;
	}
	
	
	/**
	 * Add a new item to the database.
	 * 
	 * @param newItem
	 *            The new {@link ToDoItem} that is going to be added.
	 */
	public void addToDoItem(ToDoItem newItem) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		// insert the values of the new to do item in the adapter for the database.
		values.put(sColumnIsActive, newItem.getItemActiveAsInteger());
		values.put(sColumnDescription, newItem.getItemDescription());
		values.put(sColumnDate, newItem.getItemDate());
		values.put(sColumnTime, newItem.getItemTime());
		
		db.insert(sToDoTableName, null, values);
		db.close();
		
		Log.d("Log", "New to do item added to database");
	}
	
	/**
	 * Add a list of {@link ToDoItem} to the database.
	 * 
	 * @param pages
	 *            The {@link List} of {@link ToDoItem} that are going to be added.
	 */
	public void addToDoItems(List<ToDoItem> items) {
		if (items != null) {
			for (ToDoItem i : items) {
				this.addToDoItem(i);
			}
		}
	}
	
	/**
	 * Updates a {@link ToDoItem}.
	 * 
	 * @param item
	 *            The {@link ToDoItem} that is going to be updated.
	 */
	public void updateToDoItem(ToDoItem item) {
		if(item.getItemId() != -1){
			SQLiteDatabase db = this.getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(sColumnIsActive, item.getItemActiveAsInteger());
			values.put(sColumnDescription, item.getItemDescription());
			values.put(sColumnDate, item.getItemDate());
			values.put(sColumnTime, item.getItemTime());
			db.update(sToDoTableName, values, sColumnID + "='" + item.getItemId() + "'",
					null);
			db.close();
			
			//Log.d("Log","Updating item: " + item.getItemId());
		}
	}
	
	/**
	 * Updates a list of {@link ToDoItem}.
	 * 
	 * @param items
	 *            The {@link ToDoItem} that are going to be updated.
	 */
	public void updateToDoItems(List<ToDoItem> items) {
		for (ToDoItem item : items) {
			this.updateToDoItem(item);
		}
	}
	
	/**
	 * Searches in the database for the {@link ToDoItem} with the specified id.
	 * 
	 * @param id
	 *            The ID that needs to be found.
	 * 
	 * @return Returns the requested {@link ToDoItem} or null if the
	 *         {@link ToDoItem} has not been found.
	 */
	public ToDoItem getToDoItemById(int id) {
		ToDoItem item = null;
		if (id == -1)
			return item;
		String selectQuery = "SELECT * FROM " + sToDoTableName + " WHERE "
				+ sColumnID + " = " + "'" + id + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst() && cursor.getCount() > 4) {
			item = new ToDoItem(cursor.getInt(0),(cursor.getInt(1) == 1)?true:false, cursor.getString(2),
					cursor.getString(3), cursor.getString(4));
			item.printItem();
		}
		cursor.close();
		db.close();
		return item;
	}
	
	/**
	 * Searches for all the {@link ToDoItem} inside the database.
	 * 
	 * @return Returns a {@link List} with all the {@link ToDoItem} registered within the
	 *         database.
	 */
	public List<ToDoItem> getAllToDoItems() {
		List<ToDoItem> items = new ArrayList<ToDoItem>();
		String selectQuery = "SELECT * FROM " + sToDoTableName;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				items.add(new ToDoItem(cursor.getInt(0),(cursor.getInt(1) == 1)?true:false, cursor.getString(2),
						cursor.getString(3), cursor.getString(4)));
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return items;
	}
}